package com.pokermind.dse.util;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * 数据库迁移工具类
 * 用于检查和更新数据库表结构
 */
@Component
public class DatabaseMigrationUtil {
    
    private final JdbcTemplate jdbcTemplate;
    
    public DatabaseMigrationUtil(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    /**
     * 检查并添加缺失的列
     */
    public void checkAndAddMissingColumns() {
        try {
            // 检查 enhanced_poker_hands 表的 ante 列
            if (!columnExists("enhanced_poker_hands", "ante")) {
                System.out.println("🔧 Adding missing 'ante' column to enhanced_poker_hands table...");
                jdbcTemplate.execute("ALTER TABLE enhanced_poker_hands ADD COLUMN ante DECIMAL(10,2) DEFAULT 0 AFTER big_blind");
                System.out.println("✅ Successfully added 'ante' column");
            }
            
            // 检查 players_info 是否为 JSON 类型
            if (!isJsonColumn("enhanced_poker_hands", "players_info")) {
                System.out.println("🔧 Converting 'players_info' column to JSON type...");
                jdbcTemplate.execute("ALTER TABLE enhanced_poker_hands MODIFY COLUMN players_info JSON");
                System.out.println("✅ Successfully converted 'players_info' to JSON");
            }
            
            // 检查 hero_hole_cards 是否为 JSON 类型
            if (!isJsonColumn("enhanced_poker_hands", "hero_hole_cards")) {
                System.out.println("🔧 Converting 'hero_hole_cards' column to JSON type...");
                jdbcTemplate.execute("ALTER TABLE enhanced_poker_hands MODIFY COLUMN hero_hole_cards JSON");
                System.out.println("✅ Successfully converted 'hero_hole_cards' to JSON");
            }
            
            System.out.println("🎯 Database schema migration completed successfully!");
            
        } catch (Exception e) {
            System.err.println("❌ Error during database migration: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    /**
     * 检查列是否存在
     */
    private boolean columnExists(String tableName, String columnName) {
        try {
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?";
            
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, tableName, columnName);
            return count != null && count > 0;
        } catch (Exception e) {
            System.err.println("Error checking column existence: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 检查列是否为JSON类型
     */
    private boolean isJsonColumn(String tableName, String columnName) {
        try {
            String sql = "SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?";
            
            String dataType = jdbcTemplate.queryForObject(sql, String.class, tableName, columnName);
            return "json".equalsIgnoreCase(dataType);
        } catch (Exception e) {
            System.err.println("Error checking column type: " + e.getMessage());
            return false;
        }
    }
    
    /**
     * 获取表的所有列信息
     */
    public void printTableStructure(String tableName) {
        try {
            String sql = "SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT " +
                        "FROM INFORMATION_SCHEMA.COLUMNS " +
                        "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? " +
                        "ORDER BY ORDINAL_POSITION";
            
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(sql, tableName);
            
            System.out.println("📋 Table structure for " + tableName + ":");
            System.out.println("+-----------------------+--------------+-----------+--------------+");
            System.out.println("| Column Name           | Data Type    | Nullable  | Default      |");
            System.out.println("+-----------------------+--------------+-----------+--------------+");
            
            for (Map<String, Object> column : columns) {
                System.out.printf("| %-21s | %-12s | %-9s | %-12s |\n",
                    column.get("COLUMN_NAME"),
                    column.get("DATA_TYPE"),
                    column.get("IS_NULLABLE"),
                    column.get("COLUMN_DEFAULT") != null ? column.get("COLUMN_DEFAULT").toString() : "NULL"
                );
            }
            System.out.println("+-----------------------+--------------+-----------+--------------+");
            
        } catch (Exception e) {
            System.err.println("Error printing table structure: " + e.getMessage());
        }
    }
}
